﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entidades.Espectaculos;
using System.Configuration;
using System.Data.SqlClient;

namespace Datos.Espectaculos
{
    public class EspSubTipoEspectaculoDAL
    {

        private static EspSubTipoEspectaculoDAL _instance;

        public static EspSubTipoEspectaculoDAL Instance()
        {
            if (_instance == null)
            {
                _instance = new EspSubTipoEspectaculoDAL();
            }

            return _instance;
        }
        
        public static string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();

        public List<EspSubTipoEspectaculo> ListarSubTipoEspectaculo(int idTipo, int estado)
        {
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ListarSubTipoEspectaculo]", con);
            com.Parameters.AddWithValue("@Estado", estado == -1 ? (object)DBNull.Value : estado);
            com.Parameters.AddWithValue("@IdTipo", idTipo);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                List<EspSubTipoEspectaculo> lista = new List<EspSubTipoEspectaculo>();
                EspSubTipoEspectaculo obj = null;
                while (dr.Read())
                {
                    obj = new EspSubTipoEspectaculo();
                    obj.IdSubTipo = Convert.ToInt32(dr["IdSubTipo"].ToString());
                    obj.SubTipo = dr["SubTipo"].ToString();
                    obj.Estado = Convert.ToInt32(dr["Estado"].ToString());

                    lista.Add(obj);
                }
                dr.Close();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }

        public int AgregarSubTipoEspectaculo(EspSubTipoEspectaculo obj)
        {
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_AgregarSubTipo]", con);

            com.Parameters.AddWithValue("@IdTipo", obj.IdTipo);
            com.Parameters.AddWithValue("@nombre", obj.SubTipo);
            com.Parameters.AddWithValue("@estado", obj.Estado);

            SqlParameter p = new SqlParameter();
            p.Direction = System.Data.ParameterDirection.Output;
            p.Value = 0;
            p.ParameterName = "@newIdSubtipo";

            com.Parameters.Add(p);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    result = Convert.ToInt32(com.Parameters["@newIdSubtipo"].Value);
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public int AgregarSubTipoEspectaculoLista(List<EspSubTipoEspectaculo> obj, int idTipo)
        {
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_AgregarSubTipo]", con);
           
            try
            {
                int result = 0;
                if (obj.Count > 0)
                {
                    con.Open();
                    com.Transaction = con.BeginTransaction();

                    SqlParameter p = new SqlParameter();
                    p.Direction = System.Data.ParameterDirection.Output;
                    p.Value = 0;
                    p.ParameterName = "@newIdSubtipo";
                   
                    com.CommandType = System.Data.CommandType.StoredProcedure;

                    obj.ForEach(delegate(EspSubTipoEspectaculo st)
                    {
                        com.Parameters.Clear();
                        com.Parameters.AddWithValue("@IdTipo", idTipo);
                        com.Parameters.AddWithValue("@nombre", st.SubTipo);
                        com.Parameters.AddWithValue("@estado", st.Estado);

                        com.Parameters.Add(p);

                        result += com.ExecuteNonQuery();
                    });

                    if (result == obj.Count)
                    {
                        result = 1;
                        com.Transaction.Commit();
                    }
                    else
                    {
                        result = 0;
                        com.Transaction.Rollback();
                    }
                    return result;
                }
                else { return 1; }
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public int ModificarSubTipoEspectaculo(EspSubTipoEspectaculo obj)
        {
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ModificarSubTipo]", con);

            com.Parameters.AddWithValue("@IdTipo", obj.IdTipo);
            com.Parameters.AddWithValue("@nombre", obj.SubTipo);
            com.Parameters.AddWithValue("@estado", obj.Estado);
            com.Parameters.AddWithValue("@IdSubtipo", obj.IdSubTipo);
            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }


    }
}
